#include "dblink.h"

DbLink::DbLink( wxString& tutorDir, long* plCardNum )
{
	m_plCardNum = plCardNum;
	m_bRequireNewSets = false;

	wxString sDictFileName = wxT("tanotutor_dicts.db");
	wxString sCardFileName = wxT("tanotutor_cards.db");

	m_pcDictDb = new wxSQLite3Database();
	m_pcCardDb = new wxSQLite3Database();

	m_pcDictDb->Open( tutorDir + wxT("/") + sDictFileName );
	
	if( !m_pcDictDb->IsOpen() )
	{
		wxMessageDialog( NULL, _("File: ") + sDictFileName + _("\nFailed to open database"), _("Error"), wxOK | wxICON_ERROR ).ShowModal();
			return;
	}
	
	m_pcCardDb->Open( tutorDir + wxT("/") + sCardFileName );
	
	if( !m_pcCardDb->IsOpen() )
	{
		wxMessageDialog( NULL, _("File: ") + sCardFileName + _("\nFailed to open database"), _("Error"), wxOK | wxICON_ERROR ).ShowModal();
			return;
	}
	
	// create cards table
	wxSQLite3ResultSet cCardSet = m_pcCardDb->ExecuteQuery( wxT( "SELECT name FROM sqlite_master WHERE name='cards'" ) );
	if( cCardSet.GetAsString(0) != wxT("cards") )
		m_pcCardDb->ExecuteUpdate( wxT( "CREATE TABLE cards( cardtable VARCHAR(8), cardname VARCHAR(100), primlang VARCHAR(100), tranlang VARCHAR(100), learn INTEGER )" ) );
}

DbLink::~DbLink()
{
	if( m_pcDictDb->IsOpen() )
	{
		m_pcDictDb->Close();
		delete m_pcDictDb; 
		m_pcDictDb = NULL;
	}
	
	if( m_pcCardDb->IsOpen() )
	{
		m_pcCardDb->Close();
		delete m_pcCardDb; 
		m_pcCardDb = NULL;
	}
}

void DbLink::ExecuteQueryForCardSet( const wxString& query )
{
	m_cCardSet.Finalize();
	m_cCardSet = m_pcCardDb->ExecuteQuery( query );
}

void DbLink::ExecuteQueryForWordSet( const wxString& query )
{
	m_cWordSet.Finalize();
	m_cWordSet = m_pcCardDb->ExecuteQuery( query );
	m_bRequireNewSets = false;
}

void DbLink::QueryNewCards()
{
	m_cCardSet.Finalize();

	if( m_bRequireNewSets )
	{
		// check for number of words in the cards and if zero switch off learn flag
		wxSQLite3ResultSet cCardSet = m_pcCardDb->ExecuteQuery( wxT("SELECT cardtable FROM cards WHERE learn = 1") );

		while( cCardSet.NextRow() )
		{
			wxString sCardTable = cCardSet.GetAsString(0);
			// get number of words in the card
			wxSQLite3ResultSet cResultSet = m_pcCardDb->ExecuteQuery( wxT( "SELECT count(*) FROM " )  + sCardTable );

			// if number of words is zero switch off learn flag
			if( cResultSet.GetAsString(0) == wxT("0") )
				m_pcCardDb->ExecuteQuery( wxT("UPDATE cards SET learn = 0 WHERE cardtable = '") + sCardTable + wxT("'") );

			cResultSet.Finalize();
		}

		cCardSet.Finalize();
	}

	if( *m_plCardNum )
	{
		// learn only one random card
		m_cCardSet = m_pcCardDb->ExecuteQuery( wxT("SELECT cardtable, cardname FROM cards WHERE learn = 1 ORDER BY RANDOM() LIMIT 1") );
	}
	else
	{
		// learn all cards
		m_cCardSet = m_pcCardDb->ExecuteQuery( wxT("SELECT cardtable, cardname FROM cards WHERE learn = 1 ORDER BY RANDOM()") );
	}
}

void DbLink::FinalizeSets()
{ 
	m_bRequireNewSets = true;
	m_cCardSet.Finalize(); 
	m_cWordSet.Finalize(); 
}